import sqlite3
import infiniteSitesTools as ist
from Polyspect_Operator import *
from time import strftime

class DB_Polyspect():

	dbName = '/Users/davidmorales/Documents/workspace/Demography/src/dna/db/polyspect.db'

	def createTable(self, numChromosomes):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		sql =  "CREATE TABLE 'n" + str(numChromosomes) + "' "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "segsites INT, coordinates TEXT, "
		sql += "rejectLower FLOAT, rejectUpper FLOAT, "
		sql += "nullModel TEXT, altModel TEXT, power FLOAT, "
		sql += "derivative TEXT, concavity TEXT, "
		sql += "numSimsNull INT, numSimsAlt INT)"

		print sql
		cursor.execute(sql)
	
		connection.commit()
		connection.close()

	def addPowerPoint(self, point):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()
		
		polyspect = point.getCurrentPoint().myPolyspect

		tableName = "'n" + str(polyspect.getPolyspectDimension()) + "'"
		
		sql =  "INSERT into " + tableName
		sql += " (segsites, coordinates, rejectLower, rejectUpper, power, nullModel,"
		sql += " altModel, derivative, concavity, numSimsNull, numSimsAlt) "
		sql += "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

		s = polyspect.getPolyspectSegSites()
		
		cPolyspect = polyspect.myOperator
		cDerivative = point.myCalculus.getDerivative()
		cConcavity = point.myCalculus.getConcavity()
		powerVal = point.getCurrentPoint().myPower
		
		cursor.execute(sql, (s, self.convertToSQL( cPolyspect ), polyspect.myRejectLower, \
							polyspect.myRejectUpper, powerVal, polyspect.getNullDemography(), \
							str(point.myAltDemography), \
							self.convertToSQL( cDerivative ),self.convertToSQL( cConcavity ), \
							point.getNumSims_Null(), point.getNumSims_Alt() ) )
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()
	
	def addPolyspect(self, polyspect):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		tableName = "'n" + str(polyspect.getPolyspectDimension()) + "'"
		
		sql =  "INSERT into " + tableName
		sql += " (segsites, coordinates, rejectLower, rejectUpper, power, nullModel) "
		sql += "values (?, ?, ?, ?, ?, ?)"

		s = polyspect.getPolyspectSegSites()
		
		cursor.execute(sql, (s, self.convertToSQL( polyspect.myOperator ), polyspect.myRejectLower, \
							polyspect.myRejectUpper, -1.0, polyspect.getNullDemography() ) )
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()

	def convertToSQL(self, iArray):
		s = ""
		
		for c in iArray:
			s += "%.3f;" % (c)
		
		return s

	def getAllPolyspects(self, pDimension ):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		tableName = "'n" + str(pDimension) + "'"
		
		sql =  "SELECT coordinates FROM " + tableName

		cursor.execute(sql)
		
		data = cursor.fetchall()
		#print data
	
		connection.commit()
		connection.close()
		
		polyspects = []
		
		for row in data:
			#print row
			point = np.ravel( np.zeros( pDimension ) )
			
			coords = row[0].split(';')
			
			for i in range( pDimension ):
				point[i] = float( coords[i] )
				
			polyspects.append( point )

		return polyspects
		

class DB_Pluz():

	dbName = 'db/pluz.db'

	def createTable(self):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		sql =  "CREATE TABLE biaka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()


		sql =  "CREATE TABLE mandenka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		sql =  "CREATE TABLE san "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		
		connection.close()

	def addPoint(self, name, t, a, m, v, s):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()
				
		sql =  "INSERT into " + name
		sql += " (tau, alpha, mean, variance, numSims, timestamp)"
		sql += "values (?, ?, ?, ?, ?, ?)"
		
		cursor.execute(sql, (t, a, m, v, s, strftime("%Y-%m-%d %H:%M:%S") ))
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()
	
		
class DB_Pluz1():

	dbName = 'db/pluz1.db'

	def createTable(self):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		sql =  "CREATE TABLE biaka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()


		sql =  "CREATE TABLE mandenka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		sql =  "CREATE TABLE san "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		
		connection.close()

	def addPoint(self, name, t, a, m, v, s):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()
				
		sql =  "INSERT into " + name
		sql += " (tau, alpha, mean, variance, numSims, timestamp)"
		sql += "values (?, ?, ?, ?, ?, ?)"
		
		cursor.execute(sql, (t, a, m, v, s, strftime("%Y-%m-%d %H:%M:%S") ))
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()
	
class DB_Pluz2():

	dbName = 'db/pluz2.db'

	def createTable(self):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		sql =  "CREATE TABLE biaka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()


		sql =  "CREATE TABLE mandenka "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		sql =  "CREATE TABLE san "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "tau FLOAT, alpha FLOAT, mean FLOAT, variance FLOAT, numSims INT, timestamp TEXT)"

		print sql
		cursor.execute(sql)
		connection.commit()

		
		connection.close()

	def addPoint(self, name, t, a, m, v, s):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()
				
		sql =  "INSERT into " + name
		sql += " (tau, alpha, mean, variance, numSims, timestamp)"
		sql += "values (?, ?, ?, ?, ?, ?)"
		
		cursor.execute(sql, (t, a, m, v, s, strftime("%Y-%m-%d %H:%M:%S") ))
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()
	
				
		
class DB_HOMINID_SFS():
		
	dbName = 'db/hominid.db'

	def createTable(self):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		sql =  "CREATE TABLE 'autosomes' "
		sql += "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
		sql += "locus TEXT, population TEXT, sfs TEXT)"

		print sql
		cursor.execute(sql)
	
		connection.commit()
		connection.close()

	def addSFSFile(self, filename):

		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()
		
		polyspect = point.getCurrentPoint().myPolyspect

		tableName = "'n" + str(polyspect.getPolyspectDimension()) + "'"
		
		sql =  "INSERT into " + tableName
		sql += " (segsites, coordinates, rejectLower, rejectUpper, power, nullModel,"
		sql += " altModel, derivative, concavity, numSimsNull, numSimsAlt) "
		sql += "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

		s = polyspect.getPolyspectSegSites()
		
		cPolyspect = polyspect.myOperator
		cDerivative = point.myCalculus.getDerivative()
		cConcavity = point.myCalculus.getConcavity()
		powerVal = point.getCurrentPoint().myPower
		
		cursor.execute(sql, (s, self.convertToSQL( cPolyspect ), polyspect.myRejectLower, \
							polyspect.myRejectUpper, powerVal, polyspect.getNullDemography(), \
							str(point.myAltDemography), \
							self.convertToSQL( cDerivative ),self.convertToSQL( cConcavity ), \
							point.getNumSims_Null(), point.getNumSims_Alt() ) )
	
		data = cursor.fetchall()
		print data
	
		connection.commit()
		connection.close()
	


	def convertToSQL(self, iArray):
		s = ""
		
		for c in iArray:
			s += "%.3f;" % (c)
		
		return s

	def getAllPolyspects(self, pDimension ):
		connection = sqlite3.connect( self.dbName )
		cursor = connection.cursor()

		tableName = "'n" + str(pDimension) + "'"
		
		sql =  "SELECT coordinates FROM " + tableName

		cursor.execute(sql)
		
		data = cursor.fetchall()
		#print data
	
		connection.commit()
		connection.close()
		
		polyspects = []
		
		for row in data:
			#print row
			point = np.ravel( np.zeros( pDimension ) )
			
			coords = row[0].split(';')
			
			for i in range( pDimension ):
				point[i] = float( coords[i] )
				
			polyspects.append( point )

		return polyspects
			
		
def runTest():
#
#	print "************** NEW RUN ****************"
#	print "************** NEW RUN ****************"
	print "************** NEW RUN ****************"

	db = DB_Polyspect()
	print db.getAllPolyspects(30)
#	modelPop = Dialog_MEMES()
#	
#	memes = modelPop.getResponse()
#	
#	diaPop = Dialog_Population()
#	pop = diaPop.getResponse()
#
#	nullDemography = Demography( pop.N )
#	nullDemography.addEpoch( 10000, 0, True )
#	print nullDemography
#	print pop.getMSString_Null(), nullDemography.getMSString()
#	
#	demDialog = None
#	
#	if memes == modelPop.memes[0]:
#		demDialog = Dialog_Demography_C( pop.N )
#	elif memes == modelPop.memes[1]:
#		demDialog = Dialog_Demography_G( pop.N )
#	elif memes == modelPop.memes[2]:
#		demDialog = Dialog_Demography_CC( pop.N )
#	elif memes == modelPop.memes[3]:
#		demDialog = Dialog_Demography_CG( pop.N )
#	else:
#		return
#		
#	altDemography =  demDialog.getResponse()
#		
#	simParams = SimulationParameters( pop, nullDemography )
#	numChromosomes = simParams.getNumChromosomes()
#	linOp = ist.getOp_TajimasD( numChromosomes )
#	
#	myPolyspect = Polyspect( linOp, simParams )
	
#	db = DB_Pluz2()
	#db.getAllPolyspects(30)
#	db.createTable()
	#db.addPoint( "biaka", 50.0, 2.0, 1.8, 2.3, 100 )
	

#runTest()
